package testes.vSys.dao;

import java.util.Collection;

import testes.vSys.bean.Brand;
import testes.vSys.bean.CarType;
import testes.vSys.bean.Model;
import testes.vSys.database.NullParameter;

import java.sql.ResultSet;
import java.sql.Types;

import java.util.Vector;

import testes.vSys.database.DatabaseConnection;
import testes.vSys.database.DatabaseController;

public class _hsql_ModelDao implements IModelDao {

	private Brand fillBrand(ResultSet rs) throws Exception {
		Brand brand = null;
		brand = new Brand(rs.getInt("id_brand"), rs.getString("brand_name"));

		return brand;
	}
	
	private Model fillModel(ResultSet rs)  throws Exception {
		Model model = null;
		model = new Model(rs.getInt("id_model"), new Brand(rs.getInt("id_brand"), rs.getString("brand_name")), null, rs.getString("model_name"));
		return model;
	}
	
	public Brand createBrand(Brand brand) {
		// TODO Auto-generated method stub
		DatabaseConnection conn = null;
		Brand obrand = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "INSERT INTO vs_brand (brand_name) VALUES( ? );";
            
            Integer brandId = null;

            conn.executeUpdate(sql, new Object[] {	
            		brand.getBrand() == null ? new NullParameter(Types.VARCHAR) : brand.getBrand()
            });
            
            ResultSet rs = conn.executeQuery ("CALL IDENTITY();");
            while (rs.next())
                brandId = rs.getInt(1); 
            
            if (brandId != null) 
            	obrand = new Brand(brandId, brand.getBrand());
 
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}		
        
        return obrand;
	}


	public Model createModel(Model model) {
		// TODO Auto-generated method stub
		DatabaseConnection conn = null;
		Model omodel = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "INSERT INTO vs_model (model_name, brand, vehicle_type ) VALUES(?, ?, ?);";
            
            Integer modelId = null;

            conn.executeUpdate(sql, new Object[] {	
            		model.getModel(),
            		model.getBrand().getId(), 0} );

            ResultSet rs = conn.executeQuery ("CALL IDENTITY();");
            while (rs.next())
            	modelId = rs.getInt(1); 
            
            if (modelId != null) 
            	omodel = new Model(modelId, model.getBrand(),null, model.getModel());
 
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}		
		return omodel;
	}

	public Collection<Brand> getAvaiableBrands() {
		// TODO Auto-generated method stub
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Collection<Brand> collbrand  = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "SELECT id_brand, brand_name, UPPER(brand_name) AS u_brand_name FROM vs_brand ORDER BY u_brand_name;";
            rs = conn.executeQuery(sql);
            
            collbrand = new Vector<Brand>();
            while (rs.next()) {
            	collbrand.add(fillBrand(rs));
            }

        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}		
		return collbrand;
	}

	public Collection<Model> getAvaiableModels() {
		// TODO Auto-generated method stub
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Collection<Model> collmodels  = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "SELECT id_model, model_name, brand, id_brand, brand_name, UPPER(model_name) AS u_model_name FROM vs_model, vs_brand WHERE id_brand = brand ORDER BY u_model_name;";
            rs = conn.executeQuery(sql);
            
            collmodels = new Vector<Model>();
            while (rs.next()) {
            	collmodels.add(fillModel(rs));
            }

        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}		
		return collmodels;
	}

	public Brand getBrandById(int brandId) {
		// TODO Auto-generated method stub
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Brand brand = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "SELECT * FROM vs_brand WHERE id_brand = ?;";
            rs = conn.executeQuery(sql, new Object[] { brandId });
            
            if (rs.next()) {
            	brand = fillBrand(rs);
            }

        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}		
		return brand;
	}

	public Model getModelById(int id) {
		// TODO Auto-generated method stub
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Model model = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "SELECT * FROM vs_model, vs_brand WHERE id_model = ? and id_brand = brand;";
            rs = conn.executeQuery(sql, new Object[] { id });
            
            if (rs.next()) {
            	model = fillModel(rs);
            }

        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}		
		return model;
	}

	public Collection<Model> getModelsByBrand(Brand brand) {
		// TODO Auto-generated method stub
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Collection<Model> collmodels = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "SELECT id_model, model_name, brand, id_brand, brand_name, UPPER(model_name) AS u_model_name FROM vs_model, vs_brand WHERE brand = ? and id_brand = brand ORDER BY u_model_name;";
            rs = conn.executeQuery(sql, new Object[] { brand.getId() });
            
            collmodels = new Vector<Model>();
            while (rs.next()) {
            	collmodels.add(fillModel(rs));
            }

        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}		
		return collmodels;
	}

	public boolean removeBrand(int brandId) {
		
		boolean result = false;
		DatabaseConnection conn = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "DELETE FROM vs_brand WHERE id_brand = ?; ";
            int i = conn.executeUpdate(sql, new Object[] { brandId });

            // assumindo que eh chave (volta apenas 1)
            if (i == 1){
            	result = true;
            }
            
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}

        return result;
	}

	public boolean removeModel(int modelId) {
		
		boolean result = false;
		DatabaseConnection conn = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "DELETE FROM vs_model WHERE id_model = ?; ";
            int i = conn.executeUpdate(sql, new Object[] { modelId });

            // assumindo que eh chave (volta apenas 1)
            if (i == 1){
            	result = true;
            }
            
        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}

        return result;

	}

	public Collection<CarType> getAvailableCarTypes() {
		// TODO Auto-generated method stub
		DatabaseConnection conn = null;
		ResultSet rs = null;
		Collection<CarType> collTypes  = null;

        try {
            conn = DatabaseController.getConnection();

            String sql = "SELECT * FROM vs_vehicle_type;";
            rs = conn.executeQuery(sql);
            
            collTypes = new Vector<CarType>();
            while (rs.next()) {
            	collTypes.add(new CarType(rs.getInt("id_vehicle_type"),rs.getString("vehicle_type_name")));
            }

        } catch (Exception e) {
        	e.printStackTrace();
        } finally {
			if (conn != null) {
				conn.close();
			}
		}		
		return collTypes;
	}
 
}
 
